import openpyxl
from openpyxl import Workbook
import pymysql
from datetime import datetime, date
import random
import string

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',  # 替换为你的数据库用户名
    'password': '123456',  # 替换为你的数据库密码
    'database': 'sys',  # 替换为你的数据库名
    'charset': 'utf8'
}


# 创建Excel文件并写入2000行数据
def create_excel_file(filename):
    print(f"开始创建Excel文件: {filename}")

    # 创建工作簿和工作表
    wb = Workbook()
    ws = wb.active
    ws.title = "User Data"

    # 添加表头
    headers = [
        'id', 'ideard', 'username', 'realname', 'pwd',
        'telphone', 'email', 'age', 'sex', 'address',
        'blredate', 'salary', 'job', 'company'
    ]
    ws.append(headers)

    # 生成并写入2000行随机数据
    for i in range(1, 2001):
        # 生成随机数据
        row_data = [
            i,  # id
            ''.join(random.choices(string.digits, k=18)),  # ideard (18位数字)
            f"user_{i}",  # username
            f"姓名_{i}",  # realname
            ''.join(random.choices(string.ascii_letters + string.digits, k=8)),  # pwd
            '1' + ''.join(random.choices(string.digits, k=10)),  # telphone (11位)
            f"user_{i}@example.com",  # email
            random.randint(18, 60),  # age
            random.choice(['男', '女']),  # sex
            f"地址_{random.randint(1, 100)}号",  # address
            date(random.randint(1980, 2000), random.randint(1, 12), random.randint(1, 28)),  # blredate
            round(random.uniform(3000, 20000), 2),  # salary (保留2位小数)
            random.choice(['工程师', '教师', '医生', '设计师', '销售']),  # job
            random.choice(['ABC公司', 'XYZ集团', '国际科技', '创新企业','字节'])  # company
        ]

        # 将数据写入Excel
        ws.append(row_data)

        # 每100行打印进度
        if i % 100 == 0:
            print(f"已生成 {i} 行数据...")

    # 保存Excel文件
    wb.save(filename)
    print(f"Excel文件创建完成，共 {ws.max_row} 行数据")


# 从Excel文件读取数据
def read_excel_file(filename):
    print(f"开始读取Excel文件: {filename}")

    # 打开工作簿
    wb = openpyxl.load_workbook(filename)
    ws = wb.active

    # 读取数据
    data = []
    for row in ws.iter_rows(min_row=2, values_only=True):  # 跳过表头
        # 转换日期格式为字符串 (MySQL需要YYYY-MM-DD格式)
        row = list(row)
        if isinstance(row[10], date):
            row[10] = row[10].strftime('%Y-%m-%d')

        data.append(tuple(row))

    print(f"从Excel读取到 {len(data)} 行数据")
    return data


# 创建数据库表
def create_database_table():
    try:
        # 连接数据库
        connection = pymysql.connect(**DB_CONFIG)
        cursor = connection.cursor()

        # 创建表SQL语句
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS user_info (
            id INT(11) PRIMARY KEY,
            ideard VARCHAR(50),
            username VARCHAR(50),
            realname VARCHAR(50),
            pwd VARCHAR(50),
            telphone VARCHAR(12),
            email VARCHAR(100),
            age INT(11),
            sex VARCHAR(20),
            address VARCHAR(200),
            blredate DATE,
            salary DOUBLE(9,2),
            job VARCHAR(100),
            company VARCHAR(100)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """

        # 执行创建表
        cursor.execute(create_table_sql)
        print("数据库表创建成功")

        # 关闭连接
        cursor.close()
        connection.close()
    except pymysql.Error as e:
        print(f"数据库操作错误: {e}")


# 将数据写入数据库
def write_to_database(data):
    print("开始将数据写入数据库...")

    try:
        # 连接数据库
        connection = pymysql.connect(**DB_CONFIG)
        cursor = connection.cursor()

        # 插入数据SQL
        insert_sql = """
        INSERT INTO user_info (
            id, ideard, username, realname, pwd, telphone, 
            email, age, sex, address, blredate, salary, job, company
        ) VALUES (
            %s, %s, %s, %s, %s, %s, 
            %s, %s, %s, %s, %s, %s, %s, %s
        )
        """

        # 批量插入数据
        cursor.executemany(insert_sql, data)
        connection.commit()

        print(f"成功插入 {cursor.rowcount} 条数据到数据库")

        # 关闭连接
        cursor.close()
        connection.close()
    except pymysql.Error as e:
        print(f"数据库操作错误: {e}")
        if connection:
            connection.rollback()


# 主程序
def main():
    # 定义文件名
    excel_filename = "user_data.xlsx"

    # 1. 创建Excel文件并写入数据
    create_excel_file(excel_filename)

    # 2. 从Excel读取数据
    user_data = read_excel_file(excel_filename)

    # 3. 创建数据库表
    create_database_table()

    # 4. 将数据写入数据库
    write_to_database(user_data)

    print("所有操作完成！")


if __name__ == "__main__":
    main()